#include <QSqlQuery>
#include <QSqlError>
#include <QVariant>
#include <QDebug>
#include "tbl_house_dao.h"

tbl_house_dao::tbl_house_dao(QSqlDatabase& db, QObject *parent) : QObject(parent),db(db)
{
    createTable();
}

bool tbl_house_dao::createTable()
{
    QString sql="CREATE TABLE 'tbl_house' (\
            'serial' integer NOT NULL PRIMARY KEY AUTOINCREMENT,\
            'housename' TEXT,\
            'owner' TEXT,\
            'limit' interger,\
            'starttime' TEXT,\
            'endtime' TEXT,\
            'note1' TEXT,\
            'note2' TEXT\
            );";
    QSqlQuery query(db);
    if(query.exec(sql))
        return true;
    qDebug() << "创建表tbl_house失败: " << query.lastError().text();
    return false;
}

bool tbl_house_dao::insertRow(QString &housename, QString &owner, int & limit,QString &starttime, QString &endtime, QString &msg)
{
    QString sql="insert into tbl_house (housename,owner,`limit`, starttime,endtime) values\
            (:housename,:owner,:limit,:starttime,:endtime);";
    QSqlQuery query(db);
    query.prepare(sql);
    query.bindValue(":housename", housename);
    query.bindValue(":owner", owner);
    query.bindValue(":limit", limit);
    query.bindValue(":starttime", starttime);
    query.bindValue(":endtime", endtime);

    if(query.exec())
        return true;

    msg="sql执行失败:"+query.lastError().text();
    return false;
}

bool tbl_house_dao::selectRows(QVector<QStringList> &table, QString &msg)
{
    QString sql="select serial,housename,b.nickname, owner,`limit`,starttime,endtime from \
                tbl_house a left join tbl_user b on a.owner=b.username;";
    QSqlQuery query(db);
    query.prepare(sql);
    if(!query.exec()){
        msg="sql执行失败:"+query.lastError().text();
        return false;
    }
    while(query.next()){
        QStringList list;
        list.append(query.value("serial").toString());
        list.append(query.value("housename").toString());
        list.append(query.value("nickname").toString());
        list.append(query.value("limit").toString());
        list.append(query.value("starttime").toString());
        list.append(query.value("endtime").toString());
        list.append(query.value("owner").toString());
        table.append(list);
    }
    return true;
}

int tbl_house_dao::selectLimit(int houseid, QString & msg)
{
    QString sql="select `limit` from tbl_house where serial=:houseid";
    QSqlQuery query(db);
    query.prepare(sql);
    query.bindValue(":houseid", houseid);
    qDebug() << "selectLimit::houseid="<<houseid;
    if(!query.exec()){
        msg="sql执行失败:"+query.lastError().text();
        return false;
    }
    if(query.next()){
        qDebug() << "next";
        return query.value("limit").toInt();
    }
    return 0;
}

bool tbl_house_dao::deleteRow(int &serial, QString &msg)
{
    QString sql="delete from tbl_house where serial=:serial;";
    QSqlQuery query(db);
    query.prepare(sql);
    query.bindValue(":serial", serial);

    if(query.exec())
        return true;

    msg="sql执行失败:"+query.lastError().text();
    return false;
}

bool tbl_house_dao::updateRow(int &serial, QString &housename, QString &owner, int & limit,QString &starttime,
                              QString &endtime, QString &msg)
{
    QString sql="update tbl_house set housename=:housename,owner=:owner,`limit=`:limit, "
                "starttime=:starttime,endtime=:endtime where serial=:serial;";
    QSqlQuery query(db);
    query.prepare(sql);
    query.bindValue(":housename", housename);
    query.bindValue(":owner", owner);
    query.bindValue(":limit", limit);
    query.bindValue(":starttime", starttime);
    query.bindValue(":endtime", endtime);
    query.bindValue(":serial", serial);

    if(query.exec())
        return true;

    msg="sql执行失败:"+query.lastError().text();
    return false;
}
